MS Excel Advanced Course
Course ID: 260511 0106 614ESH
Course Dates : 11/05/2026 Course Duration : 5 Studying Day/s Course Location: London, United Kingdom
Language: Bilingual
Course Category: Computer Science Programmes
Course Subcategories:
Advanced Excel & Modelling Data Analysis Data Science Information Systems Programming & Development Systems Architecture
Course Certified By: ESHub CPD & LondonUni - Executive Management Training
* Professional Training and CPD Programs
Certification Will Be Issued:
From London, United Kingdom
Course Fees:
VAT varies by course location and participant nationality.
Date has passed please contact us Sales@e-s-hub.com
Introduction
This five-day course teaches advanced Excel skills you can use immediately. It focuses on scalable reporting, data preparation, and automation. You will work with real datasets and build repeatable solutions. The goal is fewer manual steps and more reliable analysis.
Objectives
1. Build pivot-table reports and interactive dashboards with slicers and timelines.
2. Use Power Query to extract, clean, and combine data from multiple sources.
3. Create data models in Power Pivot and write DAX measures for accurate aggregation.
4. Write advanced formulas (XLOOKUP, INDEX/MATCH, dynamic arrays, LET, LAMBDA) to solve complex calculations and lookups.
5. Automate repetitive tasks with macros and basic VBA to create reproducible report workflows.
2. Use Power Query to extract, clean, and combine data from multiple sources.
3. Create data models in Power Pivot and write DAX measures for accurate aggregation.
4. Write advanced formulas (XLOOKUP, INDEX/MATCH, dynamic arrays, LET, LAMBDA) to solve complex calculations and lookups.
5. Automate repetitive tasks with macros and basic VBA to create reproducible report workflows.
Who Should Attend
1. Financial analyst responsible for monthly and ad-hoc reporting.
2. Management accountant preparing consolidated reports and forecasts.
3. Business analyst who cleans and models data for stakeholders.
4. Reporting or BI analyst building Excel-based reports and dashboards.
5. Operations manager who creates and maintains performance reports.
Training Method
• Pre-assessment
• Live group instruction
• Use of real-world examples, case studies and exercises
• Interactive participation and discussion
• Power point presentation, LCD and flip chart
• Group activities and tests
• Post-assessment
If Applicable:
• Each participant receives a 7” Tablet containing a copy of the presentation, slides and handouts
Program Support
This program is supported by:
* Interactive discussions
* Role-play
* Case studies and highlight the techniques available to the participants.
Course Agenda
Daily Schedule (Monday to Friday)
- 09:00 AM – 10:30 AM Technical Session 1
- 10:30 AM – 12:00 PM Technical Session 2
- 12:00 PM – 01:00 PM Technical Session 3
- 01:00 PM – 02:00 PM Lunch Break (If Applicable)
- Participants are expected to engage in guided self-study, reading, or personal reflection on the day’s content. This contributes toward the CPD accreditation and deepens conceptual understanding.
- 02:00 PM – 04:00 PM Self-Study & Reflection
Please Note:
- All training sessions are conducted from Monday to Friday, following the standard working week observed in the United Kingdom and European Union. Saturday and Sunday are official weekends and are not counted as part of the course duration.
- Coffee and refreshments are available on a floating basis throughout the morning. Participants may help themselves at their convenience to ensure an uninterrupted learning experience Provided if applicable and subject to course delivery arrangements.
- Lunch Provided if applicable and subject to course delivery arrangements.
Week 1
Day 1 – Advanced Excel Foundations
1. Advanced Workbook Management
- Structuring large workbooks
- Managing named ranges and tables
- Applying workbook auditing tools
2. Advanced Formulas and Functions
- XLOOKUP and INDEX/MATCH
- Dynamic array functions
- LET and LAMBDA functions
3. Preparing Data for Analysis
- Data validation techniques
- Cleaning and transforming datasets
- Organising data for reporting
Day 2 – PivotTables and Interactive Reporting
1. Advanced PivotTables
- Building multi-level PivotTables
- Custom calculations
- Grouping and summarising data
2. Interactive Dashboards
- Using slicers and timelines
- Creating KPI dashboards
- Designing user-friendly reports
3. Data Visualisation
- Advanced chart selection
- Conditional formatting techniques
- Interactive reporting features
Day 3 – Power Query and Data Preparation
1. Introduction to Power Query
- Connecting to multiple data sources
- Importing external datasets
- Query management
2. Data Transformation
- Cleaning and reshaping data
- Merging and appending queries
- Automating data preparation
3. Refreshing and Managing Queries
- Query refresh procedures
- Managing source changes
- Building repeatable data processes
Day 4 – Power Pivot and Automation
1. Power Pivot Data Models
- Creating data relationships
- Building data models
- Managing large datasets
2. DAX Fundamentals
- Creating calculated columns
- Writing DAX measures
- Aggregating business data
3. Macros and VBA Basics
- Recording macros
- Editing simple VBA code
- Automating repetitive reporting tasks
Day 5 – Practical Excel Automation Workshop
1. Reporting Solution Development
- Building an automated reporting workbook
- Integrating Power Query and PivotTables
- Creating interactive dashboards
2. Automation Exercise
- Developing reusable macros
- Testing reporting workflows
- Validating report accuracy
3. Implementation Planning
- Establishing reporting standards
- Planning future automation improvements
- Course review, feedback and implementation planning
